package io.aicn.lowcode.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class MySqlTableGenerationService {

    @Autowired
    private ObjectMapper objectMapper;

    public List<String> generateCreateTableStatements(String schemaJson) {
        try {
            @SuppressWarnings("unchecked")
            Map<String, Object> schema = objectMapper.readValue(schemaJson, Map.class);
            
            List<String> statements = new ArrayList<>();
            
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> tables = (List<Map<String, Object>>) schema.get("tables");
            
            if (tables != null) {
                for (Map<String, Object> table : tables) {
                    String sql = generateCreateTableStatement(table);
                    statements.add(sql);
                }
            }
            
            return statements;
        } catch (Exception e) {
            throw new RuntimeException("生成建表语句失败: " + e.getMessage());
        }
    }

    public String generateCreateTableStatement(Map<String, Object> tableDef) {
        StringBuilder sql = new StringBuilder();
        
        String tableName = (String) tableDef.get("tableName");
        String tableComment = (String) tableDef.get("tableComment");
        String engine = tableDef.containsKey("engine") ? (String) tableDef.get("engine") : "InnoDB";
        String charset = tableDef.containsKey("charset") ? (String) tableDef.get("charset") : "utf8mb4";
        String collation = tableDef.containsKey("collation") ? (String) tableDef.get("collation") : "utf8mb4_unicode_ci";
        
        sql.append("CREATE TABLE `").append(tableName).append("` (\n");
        
        // Columns
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) tableDef.get("columns");
        
        List<String> columnDefinitions = new ArrayList<>();
        List<String> primaryKeyDefinitions = new ArrayList<>();
        List<String> indexDefinitions = new ArrayList<>();
        List<String> foreignKeyDefinitions = new ArrayList<>();
        
        List<String> primaryKeyColumns = new ArrayList<>();
        for (Map<String, Object> column : columns) {
            String columnDef = generateColumnDefinition(column);
            columnDefinitions.add(columnDef);
            
            // Check if this column is a primary key
            if (Boolean.TRUE.equals(column.get("primaryKey"))) {
                primaryKeyColumns.add((String) column.get("name"));
            }
        }
        
        // Add primary key constraint if there are primary key columns
        if (!primaryKeyColumns.isEmpty()) {
            StringBuilder pkDef = new StringBuilder("  PRIMARY KEY (");
            for (int i = 0; i < primaryKeyColumns.size(); i++) {
                if (i > 0) pkDef.append(", ");
                pkDef.append("`").append(primaryKeyColumns.get(i)).append("`");
            }
            pkDef.append(")");
            primaryKeyDefinitions.add(pkDef.toString());
        }
        
        // Primary key handling is now done above with column-level primary key definitions
        
        // Indexes
        if (tableDef.containsKey("indexes")) {
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> indexes = (List<Map<String, Object>>) tableDef.get("indexes");
            for (Map<String, Object> index : indexes) {
                String indexDef = generateIndexDefinition(index);
                indexDefinitions.add(indexDef);
            }
        }
        
        // Foreign keys
        if (tableDef.containsKey("foreignKeys")) {
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> foreignKeys = (List<Map<String, Object>>) tableDef.get("foreignKeys");
            for (Map<String, Object> fk : foreignKeys) {
                String fkDef = generateForeignKeyDefinition(fk);
                foreignKeyDefinitions.add(fkDef);
            }
        }
        
        // Combine all definitions
        sql.append(String.join(",\n", columnDefinitions));
        
        if (!primaryKeyDefinitions.isEmpty()) {
            sql.append(",\n").append(String.join(",\n", primaryKeyDefinitions));
        }
        
        if (!indexDefinitions.isEmpty()) {
            sql.append(",\n").append(String.join(",\n", indexDefinitions));
        }
        
        if (!foreignKeyDefinitions.isEmpty()) {
            sql.append(",\n").append(String.join(",\n", foreignKeyDefinitions));
        }
        
        sql.append("\n) ENGINE=").append(engine);
        sql.append(" DEFAULT CHARSET=").append(charset);
        sql.append(" COLLATE=").append(collation);
        
        if (tableComment != null && !tableComment.isEmpty()) {
            sql.append(" COMMENT='").append(escapeSql(tableComment)).append("'");
        }
        
        sql.append(";");
        
        return sql.toString();
    }

    private String generateColumnDefinition(Map<String, Object> column) {
        StringBuilder def = new StringBuilder();
        
        String name = (String) column.get("name");
        String type = (String) column.get("type");
        Boolean nullable = column.containsKey("nullable") ? (Boolean) column.get("nullable") : false;
        Object defaultValue = column.get("defaultValue");
        String comment = (String) column.get("comment");
        Boolean autoIncrement = column.containsKey("autoIncrement") ? (Boolean) column.get("autoIncrement") : false;
        Boolean unsigned = column.containsKey("unsigned") ? (Boolean) column.get("unsigned") : false;
        
        def.append("  `").append(name).append("` ").append(type);
        
        // Handle length/precision
        if (column.containsKey("length")) {
            Integer length = (Integer) column.get("length");
            if (type.equals("VARCHAR") || type.equals("CHAR") || type.equals("BINARY") || type.equals("VARBINARY")) {
                def.append("(").append(length).append(")");
            } else if (type.equals("DECIMAL") || type.equals("NUMERIC")) {
                Integer decimals = column.containsKey("decimals") ? (Integer) column.get("decimals") : 0;
                def.append("(").append(length).append(",").append(decimals).append(")");
            }
        }
        
        // Handle ENUM/SET values
        if ((type.equals("ENUM") || type.equals("SET")) && column.containsKey("values")) {
            @SuppressWarnings("unchecked")
            List<String> values = (List<String>) column.get("values");
            def.append("(");
            for (int i = 0; i < values.size(); i++) {
                if (i > 0) def.append(",");
                def.append("'").append(escapeSql(values.get(i))).append("'");
            }
            def.append(")");
        }
        
        if (unsigned) {
            def.append(" UNSIGNED");
        }
        
        if (!nullable) {
            def.append(" NOT NULL");
        }
        
        if (autoIncrement) {
            def.append(" AUTO_INCREMENT");
        }
        
        if (defaultValue != null) {
            if (defaultValue instanceof String) {
                def.append(" DEFAULT '").append(escapeSql((String) defaultValue)).append("'");
            } else if (defaultValue instanceof Boolean) {
                def.append(" DEFAULT ").append(((Boolean) defaultValue) ? "TRUE" : "FALSE");
            } else {
                def.append(" DEFAULT ").append(defaultValue);
            }
        }
        
        if (comment != null && !comment.isEmpty()) {
            def.append(" COMMENT '").append(escapeSql(comment)).append("'");
        }
        
        return def.toString();
    }

    private String generatePrimaryKeyDefinition(Map<String, Object> primaryKey) {
        @SuppressWarnings("unchecked")
        List<String> columns = (List<String>) primaryKey.get("columns");
        String name = (String) primaryKey.get("name");
        
        StringBuilder def = new StringBuilder();
        def.append("  ");
        
        if (name != null && !name.isEmpty()) {
            def.append("CONSTRAINT `").append(name).append("` ");
        }
        
        def.append("PRIMARY KEY (");
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) def.append(", ");
            def.append("`").append(columns.get(i)).append("`");
        }
        def.append(")");
        
        return def.toString();
    }

    private String generateIndexDefinition(Map<String, Object> index) {
        String name = (String) index.get("name");
        String type = (String) index.get("type");
        @SuppressWarnings("unchecked")
        List<String> columns = (List<String>) index.get("columns");
        String comment = (String) index.get("comment");
        
        StringBuilder def = new StringBuilder();
        def.append("  ");
        
        if ("UNIQUE".equals(type)) {
            def.append("UNIQUE KEY `").append(name).append("` (");
        } else if ("FULLTEXT".equals(type)) {
            def.append("FULLTEXT KEY `").append(name).append("` (");
        } else if ("SPATIAL".equals(type)) {
            def.append("SPATIAL KEY `").append(name).append("` (");
        } else {
            def.append("KEY `").append(name).append("` (");
        }
        
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) def.append(", ");
            def.append("`").append(columns.get(i)).append("`");
        }
        def.append(")");
        
        if (comment != null && !comment.isEmpty()) {
            def.append(" COMMENT '").append(escapeSql(comment)).append("'");
        }
        
        return def.toString();
    }

    private String generateForeignKeyDefinition(Map<String, Object> fk) {
        String name = (String) fk.get("name");
        String column = (String) fk.get("column");
        String referenceTable = (String) fk.get("referenceTable");
        String referenceColumn = (String) fk.get("referenceColumn");
        String onDelete = (String) fk.get("onDelete");
        String onUpdate = (String) fk.get("onUpdate");
        
        StringBuilder def = new StringBuilder();
        def.append("  CONSTRAINT `").append(name).append("` ");
        def.append("FOREIGN KEY (`").append(column).append("`) ");
        def.append("REFERENCES `").append(referenceTable).append("` (`").append(referenceColumn).append("`)");
        
        if (onDelete != null && !onDelete.isEmpty()) {
            def.append(" ON DELETE ").append(onDelete);
        }
        
        if (onUpdate != null && !onUpdate.isEmpty()) {
            def.append(" ON UPDATE ").append(onUpdate);
        }
        
        return def.toString();
    }

    private String escapeSql(String input) {
        if (input == null) return "";
        return input.replace("'", "''");
    }
}